create table member(
	id varchar2(50) primary key,
	password varchar2(50) not null,
	name varchar2(50) not null
);
alter table member add(connectedDate Date);
create sequence sequence_no nocache;create sequence sequence_no nocache;

create table board(
	no number primary key,
	title varchar2(200) not null,
	contents varchar2(1000) not null,
	writer varchar2(50) not null,
	imgsrc varchar2(200) not null,
	imgfilename varchar2(200) not null,
	writtendate date not null,
	photodate varchar2(100) not null,
	count number default 0 not null,
	latitude varchar2(100) not null,
	longitude varchar2(100) not null,
	constraint fk_writer foreign key(writer) references member(id)
);
<<<<<<< .mine
insert into board(no,title,contents,writer,imgsrc,imgfilename,writtendate,photodate,count,latitude,longitude) values(sequence_no.nextval, '서울특별시 송파구 가락본동 100-2', 'ㅎ홓', 'jeus', 'uploadfiles/', '001.jpg', sysdate, '2012:03:12 15:21:04', 0, '37.49383','127.12117');
=======
insert into board(no,title,contents,writer,imgsrc,imgfilename,writtendate,photodate,count,latitude,longitude) values(sequence_no.nextval,'서울특별시 송파구 가락본동 100-2','ㅋ','java','uploadfiles/','001.jpg',sysdate,'2012:03:12 15:21:04',0,'37.49383','127.12117');
insert into board(no,title,contents,writer,imgsrc,imgfilename,writtendate,photodate,count,latitude,longitude) values(sequence_no.nextval, '서울특별시 송파구 가락본동 100-2', 'ㅎ홓', 'java', 'uploadfiles/', '1336457389959.JPG', sysdate, '2012:03:12 15:21:04', 0, '37.49383','127.12117');
>>>>>>> .r59
select b.no, br.replyrefno from board b, boardreply br where writer='java';
select * from board;
truncate table board;
select * from board where latitude='36.49383' and longitude='127.12117';
drop sequence sequence_no;
select * from member;
alter table board modify(photodate varchar2(100) not null);
alter table board drop column dongno;
alter table board add(photodate varchar2(100) not null);
alter table board drop column gpsinfo;
alter table board add(latitude varchar2(100) not null);
alter table board add(longitude varchar2(100) not null);

create sequence sequence_replyno nocache;
create table boardreply(
	replyno number primary key,
	replywriter varchar2(30) not null,
	replycontents varchar2(1000) not null,
	replywrittendate date not null,
	replyrefno number default 0 not null,
	replystep number default 0 not null,
	constraint fk_replywriter foreign key(replywriter) references member(id),
	constraint fk_replyrefno foreign key(replyrefno) references board(no)
);
alter table boardreply drop column replyrefno;
alter table boardreply add(replyrefno number default 0 not null);
drop sequence sequence_replyno;
delete from boardreply where replywriter='kosta';
select * from boardreply;
truncate table boardreply;

이하 5/8 매칭으로 인한 멤버테이블 변경 -이제우
create sequence sequence_memberno nocache;
alter table member add(memberno number);
select round((dbms_random.value(1,(select max(memberno) from member))),0) from dual
매칭시 상대방 아이디로 게시글 리스트 가져오는 쿼리문
SELECT no,title,count,writtendate,writer,imgsrc,imgfilename 
					    FROM(	 
								SELECT no,title,count,writtendate,CEIL(rownum/6) AS page, writer,imgsrc,imgfilename  
								FROM ( 
									SELECT no,title,count,to_char(writtendate,'YYYY.MM.DD') as writtendate,writer,imgsrc,imgfilename
									FROM board	where writer='jeus'
									order by no desc  
									 ) 
    ) WHERE PAGE=1;
    
select * from member;
select * from board;